package article_management;
import java.io.*;
import java.nio.channels.FileChannel;
import java.sql.*;
import java.util.*;

public class DAOArticle {
	private String driverName="oracle.jdbc.OracleDriver";
	private final String url="jdbc:oracle:thin:@210.115.182.197:1521:orcl";
	private final String user="sr06";
	private final String pwd="sr06";
	private ResultSet rs;
	private PreparedStatement pstmt;
	private EstablishConn conn;
	public DAOArticle(){
		//conn=new EstablishConn(driverName,url,user,pwd);
		if(conn.loadingDriver(driverName)){
			System.out.println("Successfully loaded driver");
		}else{
			System.out.println("Unsuccessfully loaded driver");
		}
		if(conn.setConnection(url, user, pwd)){
			System.out.println("Successfully connected to db");
		}else{
			System.out.println("Unsuccessfully connected to db");
		}
	}
	/**
	 * Getting all data from database
	 * @return resultset
	 */
	public ResultSet read() {
		String sql="Select * From (Select * From Articles Order By Art_ID DESC) Where Rownum <=5";
		try {
			pstmt=conn.getCon().prepareStatement(sql);
			rs=pstmt.executeQuery();
		}catch (SQLException e) {
		}
		return rs;
	}
	
	/**
	 * Insert new data into database
	 * @return resultset
	 */
	public boolean write(DTOArticle dtoArt){
		String sql="Insert Into Articles Values((Select to_number(0||max(art_id)+1)From Articles),?,?,?,?)";
		try{
			pstmt=conn.getCon().prepareStatement(sql);
			pstmt.setString(1, dtoArt.getTitle());
			pstmt.setString(2, dtoArt.getTitle());
			pstmt.setString(3, dtoArt.getDate());
			pstmt.setString(4, dtoArt.getContent());
			pstmt.executeUpdate();
			pstmt.close();
		}catch(SQLException e){
			return false;
		}
		return true;
	}
	/**
	 * Delete data From database
	 * @return true if delete success
	 * @return false if delete success
	 */
	public boolean delete(DTOArticle dtoArt){
		int id=dtoArt.getId();
		String sql="Delete From Articles Where Art_ID=?";
		try{
			pstmt=conn.getCon().prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			pstmt.close();
		}catch(SQLException e){
			return false;
		}
		return true;
	}
	/**
	 * Search data From database
	 * @return true if delete success
	 * @return false if delete success
	 */
	public ResultSet search(String keyword){
		String sql="Select * From (Select * From Article Order By Art_ID DESC) Where Art_ID like ? "
				+ "or Art_Title like ? or Art_Author like ? "
				+ "or Art_Date like ?";
		try{
			pstmt=conn.getCon().prepareStatement(sql);
			pstmt.setString(1, "%"+keyword+"%");
			pstmt.setString(2, "%"+keyword+"%");
			pstmt.setString(3, "%"+keyword+"%");
			pstmt.setString(4, "%"+keyword+"%");
			rs=pstmt.executeQuery();
		}catch(SQLException e){e.printStackTrace();}
		return rs;
	}
	/**
	 * Update data From database
	 * 	@return true if delete success
	 * 	@return false if delete success
	 */
	public boolean update(String field,String newWord,int id){
			String sql="Update Articles Set "+field+"=? Where art_id=?";
		try{
			pstmt=conn.getCon().prepareStatement(sql);	
			pstmt.setString(1, newWord);
			pstmt.setInt(2, id);
			pstmt.executeUpdate();
			pstmt.close();
		}catch(SQLException e){
			return false;
		}
		return true;
	}
	
}
